﻿#define OleDb
#define Odbc
#define Access
#define SqlCe
//#define SQLite
//#define MySql
#define SqlServer
//#define DB2
#define Oracle

using System;
using System.Text;

namespace System.Data
{
    /// <summary>
    /// 数据库工厂
    /// </summary>
    public class DbFactory
    {
        /// <summary>
        /// 获取数据库类型
        /// </summary>
        public System.Data.DatabaseType DatabaseType { get; private set; }

        private DbFactory() { }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbType"></param>
        public DbFactory(System.Data.DatabaseType dbType) { this.DatabaseType = dbType; }

        #region IDbConnection

        /// <summary>
        /// 创建数据库连接对象。
        /// </summary>
        /// <param name="connectionString"> 数据库连接字符串 </param>
        /// <returns></returns>
        public System.Data.IDbConnection CreateConnection(string connectionString = null)
        {
            System.Data.IDbConnection connection = null;
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    connection = new System.Data.Odbc.OdbcConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    connection = new System.Data.OleDb.OleDbConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    connection = new System.Data.OleDb.OleDbConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    connection = new System.Data.SqlClient.SqlConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    connection = new System.Data.OracleClient.OracleConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    connection = new System.Data.SqlServerCe.SqlCeConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    connection = new System.Data.SQLite.SQLiteConnection(connectionString);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    connection = new IBM.Data.DB2.DB2Connection(connectionString);
#endif
                    break;
                default:
                    break;
            }
            return connection;
        }

        #endregion

        #region IDbCommand

        /// <summary>
        /// 创建命令对象
        /// </summary>
        /// <param name="cmdText"> 查询的文本 </param>
        /// <param name="connection"> 与数据源的连接 </param>
        /// <param name="transaction"> 事务 </param>
        /// <returns></returns>
        public System.Data.IDbCommand CreateCommand(string cmdText = null, System.Data.IDbConnection connection = null, System.Data.IDbTransaction transaction = null)
        {
            System.Data.IDbCommand command = null;
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    command = new System.Data.Odbc.OdbcCommand(cmdText, connection as System.Data.Odbc.OdbcConnection, transaction as System.Data.Odbc.OdbcTransaction);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    command = new System.Data.OleDb.OleDbCommand(cmdText, connection as System.Data.OleDb.OleDbConnection, transaction as System.Data.OleDb.OleDbTransaction);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    command = new System.Data.OleDb.OleDbCommand(cmdText, connection as System.Data.OleDb.OleDbConnection, transaction as System.Data.OleDb.OleDbTransaction);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    command = new System.Data.SqlClient.SqlCommand(cmdText, connection as System.Data.SqlClient.SqlConnection, transaction as System.Data.SqlClient.SqlTransaction);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    command = new System.Data.OracleClient.OracleCommand(cmdText, connection as System.Data.OracleClient.OracleConnection, transaction as System.Data.OracleClient.OracleTransaction);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    command = new MySql.Data.MySqlClient.MySqlCommand(cmdText, connection as MySql.Data.MySqlClient.MySqlConnection, transaction as MySql.Data.MySqlClient.MySqlTransaction);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    command = new System.Data.SqlServerCe.SqlCeCommand(cmdText, connection as SqlServerCe.SqlCeConnection, transaction as SqlServerCe.SqlCeTransaction);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    command = new System.Data.SQLite.SQLiteCommand(cmdText, connection as SQLite.SQLiteConnection, transaction as SQLite.SQLiteTransaction);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    command = new IBM.Data.DB2.DB2Command(cmdText, connection as IBM.Data.DB2.DB2Connection, transaction as IBM.Data.DB2.DB2Transaction);
#endif
                    break;
                default:
                    break;
            }
            return command;
        }

        #endregion

        #region DbDataAdapter

        /// <summary>
        /// 创建适配器对象
        /// </summary>
        /// <param name="selectCommandText"> 查询的文本 </param>
        /// <param name="selectConnectionString"> 数据库连接字符串</param>
        /// <returns></returns>
        public System.Data.Common.DbDataAdapter CreateDataAdapter(string selectCommandText = null, string selectConnectionString = null)
        {
            System.Data.Common.DbDataAdapter dataAdapter = null;
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    dataAdapter = new System.Data.Odbc.OdbcDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    dataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    dataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    dataAdapter = new System.Data.SqlClient.SqlDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    dataAdapter = new System.Data.OracleClient.OracleDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    dataAdapter = new System.Data.SqlServerCe.SqlCeDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    dataAdapter = new System.Data.SQLite.SQLiteDataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    dataAdapter = new IBM.Data.DB2.DB2DataAdapter(selectCommandText, selectConnectionString);
#endif
                    break;
                default:
                    break;
            }
            return dataAdapter;
        }

        /// <summary>
        /// 创建适配器
        /// </summary>
        /// <param name="selectCommandText"> 查询的文本 </param>
        /// <param name="selectConnection"> 数据库连接 </param>
        /// <returns></returns>
        public System.Data.Common.DbDataAdapter CreateDataAdapter(string selectCommandText = null, System.Data.IDbConnection selectConnection = null)
        {
            System.Data.Common.DbDataAdapter dataAdapter = null;
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    dataAdapter = new System.Data.Odbc.OdbcDataAdapter(selectCommandText, selectConnection as System.Data.Odbc.OdbcConnection);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    dataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommandText, selectConnection as System.Data.OleDb.OleDbConnection);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    dataAdapter = new System.Data.OleDb.OleDbDataAdapter(selectCommandText, selectConnection as System.Data.OleDb.OleDbConnection);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    dataAdapter = new System.Data.SqlClient.SqlDataAdapter(selectCommandText, selectConnection as System.Data.SqlClient.SqlConnection);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    dataAdapter = new System.Data.OracleClient.OracleDataAdapter(selectCommandText, selectConnection as System.Data.OracleClient.OracleConnection);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    dataAdapter = new MySql.Data.MySqlClient.MySqlDataAdapter(selectCommandText, selectConnection as MySql.Data.MySqlClient.MySqlConnection);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    dataAdapter = new System.Data.SqlServerCe.SqlCeDataAdapter(selectCommandText, selectConnection as System.Data.SqlServerCe.SqlCeConnection);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    dataAdapter = new System.Data.SQLite.SQLiteDataAdapter(selectCommandText, selectConnection as System.Data.SQLite.SQLiteConnection);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    dataAdapter = new IBM.Data.DB2.DB2DataAdapter(selectCommandText, selectConnection as IBM.Data.DB2.DB2Connection);
#endif
                    break;
                default:
                    break;
            }
            return dataAdapter;
        }

        #endregion

        #region IDataParameter

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public System.Data.IDataParameter CreateParameter()
        {
            return this.CreateParameter(null, null);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="name"></param>
        /// <param name="value"></param>
        /// <returns></returns>
        public System.Data.IDataParameter CreateParameter(string name, object value)
        {
            System.Data.IDataParameter parameter = null;
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.Odbc.OdbcParameter() : new System.Data.Odbc.OdbcParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.OleDb.OleDbParameter() : new System.Data.OleDb.OleDbParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.OleDb.OleDbParameter() : new System.Data.OleDb.OleDbParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.SqlClient.SqlParameter() : new System.Data.SqlClient.SqlParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.OracleClient.OracleParameter() : new System.Data.OracleClient.OracleParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    parameter = string.IsNullOrEmpty(name) ? new MySql.Data.MySqlClient.MySqlParameter() : new MySql.Data.MySqlClient.MySqlParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.SqlServerCe.SqlCeParameter() : new System.Data.SqlServerCe.SqlCeParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    parameter = string.IsNullOrEmpty(name) ? new System.Data.SQLite.SQLiteParameter() : new System.Data.SQLite.SQLiteParameter(name, value);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    parameter = string.IsNullOrEmpty(name) ? new IBM.Data.DB2.DB2Parameter() : new IBM.Data.DB2.DB2Parameter(name, value);
#endif
                    break;
                default:
                    break;
            }
            return parameter;
        }

        #endregion

        #region 参数前缀

        private string _parameterPrefix;
        /// <summary>
        /// 获取或设置参数前缀
        /// </summary>
        public string ParameterPrefix
        {
            get
            {
                if (string.IsNullOrEmpty(this._parameterPrefix))
                {
                    if (this.DatabaseType == Data.DatabaseType.Access)
                        this._parameterPrefix = "?";
                    else if (this.DatabaseType == System.Data.DatabaseType.MySql)
                        this._parameterPrefix = "?";
                    else if (this.DatabaseType == System.Data.DatabaseType.SQLite)
                        this._parameterPrefix = "?";
                    else if (this.DatabaseType == System.Data.DatabaseType.SqlServer)
                        this._parameterPrefix = "@";
                    else if (this.DatabaseType == System.Data.DatabaseType.SqlCe)
                        this._parameterPrefix = "@";
                    else if (this.DatabaseType == System.Data.DatabaseType.Oracle)
                        this._parameterPrefix = ":";
                    else
                        this._parameterPrefix = string.Empty;
                }
                return this._parameterPrefix ?? string.Empty;
            }
            set { this._parameterPrefix = value; }
        }

        #endregion

        /// <summary>
        /// 获取数据库类型
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static System.Data.DatabaseType GetDatabase(string dbType)
        {
            object obj = Enum.Parse(typeof(System.Data.DatabaseType), dbType, true);
            if (obj != null && obj is System.Data.DatabaseType)
                return (System.Data.DatabaseType)obj;
            else
                return default(System.Data.DatabaseType);
        }

        #region Schema

        public System.Data.DataTable GetSchema(System.Data.IDbConnection connection, string collectionName = null, params string[] restrictionValues)
        {
            var source = new System.Data.DataTable();
            switch (this.DatabaseType)
            {
                case System.Data.DatabaseType.Odbc:
#if Odbc
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.Odbc.OdbcConnection)connection).GetSchema() : ((System.Data.Odbc.OdbcConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.OleDb:
#if OleDb
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.OleDb.OleDbConnection)connection).GetSchema() : ((System.Data.OleDb.OleDbConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.Access:
#if Access
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.OleDb.OleDbConnection)connection).GetSchema() : ((System.Data.OleDb.OleDbConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.SqlServer:
#if SqlServer
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.SqlClient.SqlConnection)connection).GetSchema() : ((System.Data.SqlClient.SqlConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.Oracle:
#if Oracle
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.OracleClient.OracleConnection)connection).GetSchema() : ((System.Data.OracleClient.OracleConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.MySql:
#if MySql
                    source = string.IsNullOrEmpty(collectionName) ? ((MySql.Data.MySqlClient.MySqlConnection)connection).GetSchema() : ((MySql.Data.MySqlClient.MySqlConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.SqlCe:
#if SqlCe
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.SqlServerCe.SqlCeConnection)connection).GetSchema() : ((System.Data.SqlServerCe.SqlCeConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.SQLite:
#if SQLite
                    source = string.IsNullOrEmpty(collectionName) ? ((System.Data.SQLite.SQLiteConnection)connection).GetSchema() : ((System.Data.SQLite.SQLiteConnection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                case System.Data.DatabaseType.DB2:
#if DB2
                    source = string.IsNullOrEmpty(collectionName) ? ((IBM.Data.DB2.DB2Connection)connection).GetSchema() : ((IBM.Data.DB2.DB2Connection)connection).GetSchema(collectionName, restrictionValues);
#endif
                    break;
                default:
                    break;
            }
            return source;
        }

        public System.Data.DataTable GetTables(System.Data.IDbConnection connection)
        {
            return this.GetSchema(connection, "Tables");
        }

        public System.Data.DataTable GetColumns(System.Data.IDbConnection connection)
        {
            return this.GetSchema(connection, "Columns");
        }

        /// <summary>
        /// 获取索引列
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public System.Data.DataTable GetIndexColumns(System.Data.IDbConnection connection)
        {
            return this.GetSchema(connection, "IndexColumns");
        }

        public System.Data.DataTable GetViews(System.Data.IDbConnection connection)
        {
            return this.GetSchema(connection, "Views");
        }

        public System.Data.DataTable GetDataTypes(System.Data.IDbConnection connection)
        {
            return this.GetSchema(connection, "DataTypes");
        }

        #endregion

        #region DateTime

        /// <summary>
        /// 
        /// </summary>
        /// <param name="connection"></param>
        /// <returns></returns>
        public string GetDateTimeSql
        {
            get
            {
                //  获取时间sql语句
                string sql = string.Empty;
                //  判断数据库类型
                switch (this.DatabaseType)
                {
                    case DatabaseType.Odbc:
                        break;
                    case DatabaseType.OleDb:
                        break;
                    case DatabaseType.Access:
                        sql = @"select now()";
                        break;
                    case DatabaseType.SqlServer:
                        sql = @"select getdate()";
                        break;
                    case DatabaseType.Oracle:
                        sql = @"select sysdate from dual";
                        break;
                    case DatabaseType.MySql:
                        sql = @"select now()";
                        break;
                    case DatabaseType.SqlCe:
                        break;
                    case DatabaseType.SQLite:
                        sql = @"select datetime('now')";
                        break;
                    default:
                        break;
                }
                return sql;
            }
        }

        #endregion
    }
}
